package com.genius.workflow.util;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.regex.Matcher;

import org.apache.commons.lang3.StringUtils;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.genius.workflow.config.DefaultConfiguration;
import com.genius.workflow.element.SQLBean;
import com.genius.workflow.page.PageResult;
import com.genius.workflow.page.SearchResult;
import com.genius.workflow.page.SqlLang;
import com.genius.workflow.util.DBTools.DBType;

public class DBTools2JiuKu {
	public static SqlLang getSelectSqlByDBType(DBType type,String headField,String select,int page,int limit,String ascField,String asc,String whereSearchCond,String whereHighSearchCond){
		SqlLang sql = null;
		switch(type){
			case MySql:
				sql = selectByMySql(headField,select, page, limit, ascField, asc, whereSearchCond, whereHighSearchCond);
				break;
			case Oracle:
				sql = selectByOracle(headField,select, page, limit, ascField, asc, whereSearchCond, whereHighSearchCond);
				break;
			case SqlServer:
				sql = selectBySqlServer(headField,select, page, limit, ascField, asc, whereSearchCond, whereHighSearchCond);
				break;
		}
		return sql;
	}
	private static SqlLang selectByMySql(String headField,String select,int page,int limit,String ascField,String asc,String whereSearchCond,String whereHighSearchCond){
		String lowerCaseSelect = select.toLowerCase();
		String where = getWhere(lowerCaseSelect, whereSearchCond, whereHighSearchCond);
		return DBToolsJiuKu.selectByMySql(headField, select, page, limit, ascField, asc, where);
	}
	private static SqlLang selectByOracle(String headField,String select,int page,int limit,String ascField,String asc,String whereSearchCond,String whereHighSearchCond){
		String lowerCaseSelect = select.toLowerCase();
		String where = getWhere(lowerCaseSelect, whereSearchCond, whereHighSearchCond);
		return DBToolsJiuKu.selectByOracle(headField, select, page, limit, ascField, asc, where);
	}
	private static SqlLang selectBySqlServer(String headField,String select,int page,int limit,String ascField,String asc,String whereSearchCond,String whereHighSearchCond){
		String lowerCaseSelect = select.toLowerCase();
		String where = getWhere(lowerCaseSelect, whereSearchCond, whereHighSearchCond);
		
		return DBToolsJiuKu.selectBySqlServer(headField, select, page, limit, ascField, asc, where);
	}
	private static String getWhere(String lowerCaseSelect,String whereSearchCond,String whereHighSearchCond){
		String where = "";
		
		if(StringUtils.isNotBlank(whereSearchCond)){
			where += whereSearchCond+" and ";
		}
		if(StringUtils.isNotBlank(whereHighSearchCond)){
			where += whereHighSearchCond+" and ";
		}
		if(StringUtils.isNotBlank(where))
			where = where.substring(0, where.lastIndexOf("and "));
		return where;
	}
	//新方法 直接获取传过来的条件字段
	public static JSONObject getWhereFieldByParameter(JSONObject json){
		JSONObject parameter = new JSONObject();
		if(json.containsKey(PageResult.parameter)){
			parameter.putAll(json.getJSONObject(PageResult.parameter));
		}
		return parameter;
	}
	/**
	 * 根据sql获取 =?前的参数名称
	 * @param sql
	 * @return
	 */
	public static String [] getFieldNameByParameter(String sql){
		return DBToolsJiuKu.getFieldNameByParameter(sql);
	}
	/**
	 * 合并查询结果集到一个json内
	 * @param json
	 * @param searchResult
	 * @param highSearchResult
	 * @return
	 */
	public static JSONObject mergeAll(JSONObject json,SearchResult searchResult,SearchResult highSearchResult){
		json.putAll(searchResult.getJson());
		json.putAll(highSearchResult.getJson());
		return json;
	}
	/**
	 * 合并查询结果集到一个json内
	 * @param json
	 * @param searchResult
	 * @param highSearchResult
	 * @return
	 */
	public static JSONObject mergeAll(JSONObject json,SearchResult searchResult){
		json.putAll(searchResult.getJson());
		return json;
	}
	/**
	 * 根据参数名称从json中提取参数值
	 * @param parmeters
	 * @param json
	 * @return
	 */
	public static String [] parsePermeterByField(String [] parmeters,JSONObject json){
		return DBToolsJiuKu.parsePermeterByField(parmeters, json);
	}
	public static String [] parsePermeterBySql(String sql,JSONObject json){
		return parsePermeterByField(getFieldNameByParameter(sql), json);
	}
	public static SearchResult getWhereFieldBySearch(JSONObject json){
		return DBToolsJiuKu.getWhereFieldBySearch(json);
	}
	public static SearchResult getWhereFieldByHighSearch(JSONObject json){
		return DBToolsJiuKu.getWhereFieldByHighSearch(json);
	}
	public static JSONObject getIsExist(String whereField,String table,JSONObject json) throws ConnectionException, DBClosedException, SQLException{
		return DBToolsJiuKu.getIsExist(whereField, table, json);
	}
	/**
	 * 获取sql语句
	 * @param headField
	 * @param select
	 * @param page
	 * @param limit
	 * @param ascField
	 * @param asc
	 * @param whereSearchCond 根据普通查询组件获取sql
	 * @param whereHighSearchCond 根据高级查询组件获取sql
	 * @return
	 */
	public static SqlLang getSelectPageSql(String headField,String select,int page,int limit,String ascField,String asc,String whereSearchCond,String whereHighSearchCond){
		return getSelectSqlByDBType(DefaultConfiguration.geniusDBType, headField,select, page, limit, ascField, asc, whereSearchCond,whereHighSearchCond);
	}
	public static SqlLang getSelectPageSql(String headField,String select,int page,int limit,String ascField,String asc,String whereSearchCond){
		return getSelectSqlByDBType(DefaultConfiguration.geniusDBType, headField,select, page, limit, ascField, asc, whereSearchCond,"");
	}
	public static SqlLang getSelectPageSql(String headField,String select,String whereSearchCond){
		return getSelectSqlByDBType(DefaultConfiguration.geniusDBType, headField,select, 0, 0, null, null, whereSearchCond,"");
	}
	/**
	 * 获取sql语句
	 * @param headField
	 * @param select
	 * @param page
	 * @param limit
	 * @param ascField
	 * @param asc
	 * @return
	 */
	public static SqlLang getSelectPageSql(String headField,String select,int page,int limit,String ascField,String asc){
		return getSelectSqlByDBType(DefaultConfiguration.geniusDBType, headField,select, page, limit, ascField, asc, "","");
	}
	/**
	 * 获取sql语句
	 * @param headField
	 * @param select
	 * @param page
	 * @param limit
	 * @param ascField
	 * @param asc
	 * @return
	 */
	public static SqlLang getSelectPageSql(String headField,String select,int page,int limit){
		return getSelectSqlByDBType(DefaultConfiguration.geniusDBType, headField,select, page, limit, null, null, "","");
	}
	public static PageResult selectByCount(SqlLang sql,String [] parameterArr) throws ConnectionException, DBClosedException, SQLException{
		return DBUtilsJiuKu.selectByCount(sql,parameterArr);
	}
	public static SqlLang getExportSql(String headField,String select,String whereSearchCond){
		return getSelectSqlByDBType(DefaultConfiguration.geniusDBType, headField,select, 0, 0, null, null, whereSearchCond, "");
	}
	public static int[] insert(String insertField,String table,JSONObject json) throws ConnectionException, DBClosedException, SQLException {
		String insertSql = DBToolsJiuKu.getInsertSql(insertField, table);
		String [] parms = DBToolsJiuKu.getInsertParms(insertField, json);
		return insert(insertSql,parms);
	}
	public static int [] insert(String sql,String [] parms) throws ConnectionException, DBClosedException, SQLException {
		return DBToolsJiuKu.insert(sql, parms);
	}
	/**
	 * 批量插入数据操作
	 * @param insertField
	 * @param table
	 * @param json
	 * @throws ConnectionException
	 * @throws DBClosedException
	 * @throws SQLException
	 */
	public static void insertBatch(String insertField,String table,JSONArray arr) throws ConnectionException, DBClosedException, SQLException {
		DBToolsJiuKu.insertBatch(insertField, table,arr);
	}
	public static void insertBatch(String sql,List<String[]> parms) throws ConnectionException, DBClosedException, SQLException {
 		DBToolsJiuKu.insertBatch(sql,parms);
	}
	public static void insertBatch(LinkedList<SQLBean> sqlBean) throws ConnectionException, DBClosedException, SQLException {
		DBToolsJiuKu.executeBatch(sqlBean);
	}
	/**
	 * 更新操作
	 * @param updateField
	 * @param whereField
	 * @param table
	 * @param json
	 * @throws ConnectionException
	 * @throws DBClosedException
	 * @throws SQLException
	 */
	public static void update(String updateField,String whereField,String table,JSONObject json) throws ConnectionException, DBClosedException, SQLException{
		DBToolsJiuKu.update(updateField,whereField,table,json);
	}
	public static void update(String sql,String [] parms) throws ConnectionException, DBClosedException, SQLException{
 		DBToolsJiuKu.update(sql,parms);
	}
	/**
	 * 批量更新操作
	 * @param updateField
	 * @param whereField
	 * @param table
	 * @param json
	 * @throws ConnectionException
	 * @throws DBClosedException
	 * @throws SQLException
	 */
	public static void updateBatch(String updateField,String whereField,String table,JSONArray arr) throws ConnectionException, DBClosedException, SQLException{
		DBToolsJiuKu.updateBatch(updateField, whereField, table, arr);
	}
	/**
	 * 批量更新操作
	 * 争对一个sql，不同参数在一个数据库连接内执行
	 * @throws SQLException
	 */
	public static void updateBatch(String sql,List<String[]> parms) throws ConnectionException, DBClosedException, SQLException{
 		DBToolsJiuKu.updateBatch(sql, parms);
	}
	//争对不同sql，不同参数在一个数据库连接内执行
	public static void updateBatch(LinkedList<SQLBean> sqlBean) throws ConnectionException, DBClosedException, SQLException{
 		DBToolsJiuKu.executeBatch(sqlBean);
	}
	/**
	 * 删除数据操作
	 * @param whereField
	 * @param table
	 * @param json
	 * @throws ConnectionException
	 * @throws DBClosedException
	 * @throws SQLException
	 */
	public static void delete(String whereField,String table,JSONObject json) throws ConnectionException, DBClosedException, SQLException{
		DBToolsJiuKu.delete(whereField, table, json);
	}
	public static void delete(String sql,String [] parms) throws ConnectionException, DBClosedException, SQLException{
		DBToolsJiuKu.delete(sql, parms);
	}
	/**
	 * 删除数据操作
	 * @param whereField
	 * @param table
	 * @param json
	 * @throws ConnectionException
	 * @throws DBClosedException
	 * @throws SQLException
	 */
	public static void deleteBach(String whereField,String table,JSONArray arr) throws ConnectionException, DBClosedException, SQLException{
		DBToolsJiuKu.deleteBach(whereField, table, arr);
	}
	public static void deleteBach(String sql,List<String[]>parms) throws ConnectionException, DBClosedException, SQLException{
		DBToolsJiuKu.deleteBach(sql, parms);
	}
	public static void deleteBach(LinkedList<SQLBean> sqlBean) throws ConnectionException, DBClosedException, SQLException{
		DBToolsJiuKu.executeBatch(sqlBean);
	}
	/**
	 * 查询操作
	 * @param selectField
	 * @param table
	 * @throws ConnectionException
	 * @throws DBClosedException
	 * @throws SQLException
	 */
	public static JSONObject select(String selectField,String table) throws ConnectionException, DBClosedException, SQLException{
		return DBToolsJiuKu.select(selectField, table, null,null);
	}
	public static JSONObject select(String sql,Object ... params) throws ConnectionException, DBClosedException, SQLException{
 		return DBToolsJiuKu.select(sql, params);
	}
	/**
	 * 查询操作
	 * @param selectField 查询的字段
	 * @param table 查询的表名
	 * @param whereField where条件字段
	 * @throws ConnectionException
	 * @throws DBClosedException
	 * @throws SQLException
	 */
	public static JSONObject select(String selectField,String table,String whereField,JSONObject json) throws ConnectionException, DBClosedException, SQLException{
		return DBToolsJiuKu.select(selectField, table, whereField, json);
	}
	/**
	 * 查询操作
	 * @param selectField 查询的字段
	 * @param table 查询的表名
	 * @param whereField where条件字段
	 * @throws ConnectionException
	 * @throws DBClosedException
	 * @throws SQLException
	 */
	public static JSONArray selectArr(String selectField,String table) throws ConnectionException, DBClosedException, SQLException{
		
		return DBToolsJiuKu.selectArr(selectField, table, null, null);
	}
	/**
	 * 批量查询操作
	 * @param selectField 查询的字段
	 * @param table 查询的表名
	 * @param whereField where条件字段
	 * @throws ConnectionException
	 * @throws DBClosedException
	 * @throws SQLException
	 */
	public static JSONArray selectArr(String selectField,String table,String whereField,JSONObject json) throws ConnectionException, DBClosedException, SQLException{
		
		return DBToolsJiuKu.selectArr(selectField, table, whereField, json);
	}
	public static JSONArray selectArr(String sql,Object... params) throws ConnectionException, DBClosedException, SQLException{
		
		return DBUtilsJiuKu.selectArr(sql, params);
	}
	public static int [] executeBatch(LinkedList<SQLBean>beanList) throws ConnectionException, DBClosedException, SQLException{
 		return DBToolsJiuKu.executeBatch(beanList);
	}
	public static int [] execute(SQLBean bean) throws ConnectionException, DBClosedException, SQLException{
 		return DBToolsJiuKu.execute(bean);
	}
	public static String [] getSelectFieldFromSql(String sql){
		String sqlToLower = sql.toLowerCase();
		int selectLength = sqlToLower.indexOf("select ");
		int fromLength = sqlToLower.indexOf(" from ");
		String fields = sql.substring(selectLength+7, fromLength);
		String [] filedsArr = fields.split(",");
		return getSelectFieldBySpace(filedsArr);
	}
	public static String getSelectFieldFromSqlStr(String sql){
		String sqlToLower = sql.toLowerCase();
		int selectLength = sqlToLower.indexOf("select ");
		int fromLength = sqlToLower.indexOf(" from ");
		String fields = sql.substring(selectLength+7, fromLength);
		String [] filedsArr = fields.split(",");
		return StringUtils.join(getSelectFieldBySpace(filedsArr),DefaultConfiguration.separator_douhao);
	}
	private static String [] getSelectFieldBySpace(String [] filedsArr){
		String [] newFields = new String[filedsArr.length];
		for(int index=0;index<filedsArr.length;index++){
			String field = filedsArr[index];
			field = field.replaceAll("^\\s*", "") ;//去掉开头的所有空格
			field = field.replaceAll("\\s*$", "") ;//去掉结尾的所有空格
			field = field.replaceAll("\\)", " ") ;//去掉结尾为)并转成空格
			field = field.replaceAll("\\.", " ") ;//把.转成空格
			int last = field.lastIndexOf(" ");
			newFields[index] = field.substring(last+1);
		}
		return newFields;
	}
	
}
